package DB;

import Model.*;

import java.sql.*;
import java.util.ArrayList;

public class DBUdlej_Salg_Vare {
	
	private Connection con;
	
	private static DBUdlej_Salg_Vare instance = null;
    
    public static DBUdlej_Salg_Vare getInstance()
    {
        if (instance == null)
        {
          instance = new DBUdlej_Salg_Vare();
        }
        return instance;
    }
	
	public DBUdlej_Salg_Vare()
	{
		con = DBConnection.getInstance().getDBcon();
	}
	
	private void create(String wClause)
	{
	    String query="INSERT INTO Udlej_Salg_Vare ";

		if (wClause.length()>0)
		{
			query=query+ wClause;
			
			Statement stmt;
			
			try
			{
				stmt = con.createStatement(); 
				stmt.executeUpdate(query); 
				con.commit(); 
				stmt.close();
			}
			catch (Exception e)
			{ 
				e.printStackTrace(); 
			}
		}
		else
		{
		}
	}
	
	public void createUdlej_Salg_Vare(Udlej_Salg_Vare usv)
	{
		String wClause = "VALUES " 
			+ "(" + usv.getF().getFormatid() + ","
			+ "" + usv.getUs().getUsid() + "," //
			+ "" + usv.getAntal() + ","
			+ "" + usv.getPris() + ")";
	        create(wClause);
	}
	
	public void deleteUdlej_Salg_Vare(int usid)
	{
		PreparedStatement prepstmt;
		try 
		{     
		  prepstmt = con.prepareStatement
		    ("DELETE FROM Udlej_Salg_Vare WHERE usid = " + usid + "");   
		  prepstmt.executeUpdate();          
		  con.commit();
		  prepstmt.close();
		}
		catch (Exception e) 
		{
		  e.printStackTrace();
		}
	} 
	
	private String buildQuery(String wClause)
	{
	    String query="SELECT * FROM Udlej_Salg_Vare";
		
		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;
			
		return query;
	}
	
	private Udlej_Salg_Vare singleWhere(String wClause, boolean retrieveAssocation)
	{
		ResultSet results;
		Udlej_Salg_Vare usv = new Udlej_Salg_Vare();
	    String query =  buildQuery(wClause);
		try{
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
	 		if( results.next() ){
                            usv = buildUdlej_Salg_Vare(results, retrieveAssocation);
                            stmt.close();
			}
		
		}//end try	
	 	catch(Exception e){
	 		System.out.println("Query exception: "+e);
	 	}
		return usv;
	}
	
	public ArrayList<Udlej_Salg_Vare> getAlleUdlej_Salg_Vare()
	{
		return miscWhere("");
	}
	
	private ArrayList<Udlej_Salg_Vare> miscWhere(String wClause)
	{
		ResultSet results;
		ArrayList list = new ArrayList();	
		
		String query =  buildQuery(wClause);
  
  		try
  		{ // read the customer from the database
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);		 		

			while( results.next() )
			{
		     	 Udlej_Salg_Vare usvObj = new Udlej_Salg_Vare();
		     	 usvObj = buildUdlej_Salg_Vare(results, true);	
                 list.add(usvObj);	
			}//end while
                      
			stmt.close();

		}//slut try	
	 	catch(Exception e)
	 	{
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return list;
	}
	
	public ArrayList<Udlej_Salg_Vare> usvViaKunde(int kundeid)
	{
		ResultSet results;
		ArrayList list = new ArrayList();	
		
		String query =  "SELECT formatid,Udlej_Salg_Vare.usid,Udlej_Salg_Vare.eksemplarid,antal,Udlej_Salg_Vare.pris,type FROM Udlej_Salg_Vare, Udlej_Salg WHERE "+ kundeid +" = kundeid AND Udlej_Salg.usid = Udlej_Salg_Vare.usid";
  		try
  		{ // read the customer from the database
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);		 		

			while( results.next() )
			{
		     	 Udlej_Salg_Vare usvObj = new Udlej_Salg_Vare();
		     	 usvObj = buildUdlej_Salg_Vare(results, true);	// false fordi vi altid skal finde udlejningen af en kunde og ikke salget.
                 list.add(usvObj);	
			}//end while
                      
			stmt.close();

		}//slut try	
	 	catch(Exception e)
	 	{
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return list;
	}
	
	private Udlej_Salg_Vare buildUdlej_Salg_Vare(ResultSet results, boolean retrieveAssocation)
    {   
		DBUdlej_Salg dbUs = new DBUdlej_Salg();
		DBFormat dbFor = DBFormat.getInstance();
		DBEksemplar dbEks = DBEksemplar.getInstance();
		Udlej_Salg_Vare usv = new Udlej_Salg_Vare();
          try
          {
        	  	if(retrieveAssocation)
        	  	{
        	  		if(results.getObject(1)!=null)
        	  		{
        	  			usv.setF(dbFor.findFormat(results.getInt(1), false));
        	  		}
        	  		
        	  		
        	  		if(results.getObject(3)!=null)
        	  		{
        	  			usv.setE(dbEks.findEksemplar(results.getInt(3),false));
        	  		}
        	  		else
        	  		{
        	  			usv.setE(new Eksemplar());
        	  		}
        	  	}
        	  	usv.setUs(dbUs.findUdlej_Salg(results.getInt(2),false));
                usv.setAntal(results.getInt(4));
                usv.setPris(results.getDouble(5));
                usv.setType(results.getString(6));
          }
         catch(Exception e)
         {
             System.out.println("error in building the USV object" + e);
         }
         return usv;
    }
    
	public Udlej_Salg_Vare findUdlej_Salg_Vare(int usid, boolean retrieveAssocation) 
    {
    	String wClause = "  usid = " + Integer.toString(usid) + "";
    	return singleWhere(wClause,retrieveAssocation);
    }
	
	public ArrayList<Udlej_Salg_Vare> findUdlejSalgVare(int usId, boolean retrieveAssocation) 
    {
    	String query = " SELECT * from Udlej_Salg_Vare where " + Integer.toString(usId) + " = usid";
    	ResultSet results;
		ArrayList list = new ArrayList();	
  
  		try
  		{ // read the customer from the database
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);		 		

			while( results.next() )
			{
		     	 Udlej_Salg_Vare usvObj = new Udlej_Salg_Vare();
		     	 usvObj = buildUdlej_Salg_Vare(results, retrieveAssocation);	
                 list.add(usvObj);	
			}//end while
                      
			stmt.close();

		}//slut try	
	 	catch(Exception e)
	 	{
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return list;
    }
	
	public ArrayList<Udlej_Salg_Vare> findUsvViaFormat(int fId, boolean retrieveAssocation) 
    {
    	String query = " SELECT * from Udlej_Salg_Vare where " + fId + " = formatid";
    	ResultSet results;
		ArrayList list = new ArrayList();	
  
  		try
  		{ // read the customer from the database
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);		 		

			while( results.next() )
			{
		     	 Udlej_Salg_Vare usvObj = new Udlej_Salg_Vare();
		     	 usvObj = buildUdlej_Salg_Vare(results, retrieveAssocation);	
                 list.add(usvObj);	
			}//end while
                      
			stmt.close();

		}//slut try	
	 	catch(Exception e)
	 	{
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return list;
    }
	public Udlej_Salg_Vare findUdlej_Salg_VareViaEksemplar(int eId, boolean retrieveAssocation) 
    {
    	String query = " SELECT * from Udlej_Salg_Vare where " + eId + " = eksemplarid";
    	ResultSet results;
    	Udlej_Salg_Vare usvObj = new Udlej_Salg_Vare();
  		try
  		{ // read the customer from the database
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);		 		

			while( results.next() )
			{
		     	 usvObj = buildUdlej_Salg_Vare(results, retrieveAssocation);
			}//end while
                      
			stmt.close();

		}//slut try	
	 	catch(Exception e)
	 	{
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return usvObj;
    }
	
}
